import { App } from "@/models/app";
import { useAsyncState, useStorage } from "@vueuse/core";
import initSqlJs, { Statement } from "sql.js";
import { Database, SqlJsStatic } from "sql.js";
import { Ref, computed, ref, watch } from "vue";
import { dbFile2bytes } from "./utils";
import { TWebResourcesService } from "@/services/webResourceServices";

export type TDbServices = ReturnType<typeof getServices>
export type TQueryAllResult = ReturnType<TDbServices['queryAll']>

export function getServices(app: App, wasm_base64: { file: any }, webResourcesServices: TWebResourcesService) {
    const { isReady, sqlEngine, sqlDb, dbFileBs64Storage } = initDb(app, wasm_base64, webResourcesServices);

    const tableInfoManager = createTableInfoManager(queryAll)

    function uploadDbFile(file: File) {

        let reader = new FileReader();

        reader.onload = async function (e) {
            dbFileBs64Storage.value = e.target?.result as string
            sqlDb.value = new sqlEngine.value!.Database(await dbFile2bytes(dbFileBs64Storage.value))
        }

        reader.readAsText(file, 'utf8')
    }

    function queryAll(code: string) {
        let stmt: Statement | null = null


        let fields = [] as string[]
        const rows = [] as initSqlJs.ParamsObject[]

        try {
            stmt = sqlDb.value.prepare(code);
            fields = stmt.getColumnNames()

            stmt.bind({});
            while (stmt.step()) {
                const row = stmt.getAsObject()
                rows.push(row)
            }

            function* toRowsArrayHasHeader() {
                yield fields
                for (const r of rows) {
                    yield fields.map(f => r[f])
                }
            }

            /**
             * Returns data based on the results
             */
            function inferData() {
                if (rows.length === 0) {
                    return null
                }
                const cols = fields.length
                if (cols === 1) {

                    if (rows.length === 1) {
                        return rows[0][fields[0]]
                    }

                    return rows.map(v => v[fields[0]])
                }

                return rows
            }

            return {
                fields, rows,
                toRowsArrayHasHeader,
                inferData
            }


        } catch (error) {
            console.trace(error)
            throw new Error(`${error};\nsql: ${code}`);
        } finally {
            stmt?.free()
        }
    }

    function runOnTransaction<TReturn>(handler: () => TReturn, type: 'COMMIT' | 'ROLLBACK' = 'COMMIT') {
        try {
            sqlDb.value.run('BEGIN')
            return handler()
        } catch (error) {
            throw error
        } finally {
            sqlDb.value.run(type)
        }
    }

    function query2tempory(tableName: string, query: string) {
        const sqlstr = `CREATE TABLE ${tableName} as ${query}`
        sqlDb.value.run(sqlstr)
        return tableName
    }


    function parse2sqlValueBaseFieldType(table: string, field: string, value: any) {
        const colType = tableInfoManager.getFieldType(table, field)

        if (colType === 'INTEGER') {
            return parseInt(value, 10)
        }
        if (colType === 'float') {
            return parseFloat(value)
        }


        if (typeof value === 'string') {
            return `"${value}"`
        }
        if (value === null) {
            return 'null'
        }
        return value
    }


    /*
       'a' =>  `'a'`
       1 => 1
    */
    function parse2sqlValue(value: any) {
        if (typeof value === 'string') {
            return `"${value}"`
        }
        if (value === null) {
            return 'null'
        }
        return value
    }

    /*
       ['a',1] =>  [`'a'`,1]
    */
    function valuesArray2sqlArray(values: any[]) {
        return values.map(parse2sqlValue)
    }

    /**
     * const {values,hasNull} = extractNullInValues([1,'a',null])
     * hasNull == True
     * values == [1,'a']
     */
    function extractNullInValues(values: any[]) {
        const res = values.filter(v => v !== null)
        const hasNull = values.length > res.length

        return {
            hasNull,
            values: res
        }
    }


    return {
        isReady,
        uploadDbFile,
        queryAll,
        query2tempory,
        runOnTransaction,
        getTableFields: tableInfoManager.getTableFields,
        getFieldType: tableInfoManager.getFieldType,
        parse2sqlValueBaseFieldType,
        parse2sqlValue,
        valuesArray2sqlArray,
        extractNullInValues

    }
}



type TMetaRow = {
    name: string
    type: 'INTEGER' | 'TEXT' | 'INTEGER' | 'float' | 'date'
}
function createTableInfoManager(queryAll: TDbServices['queryAll']) {
    const mTableCache = new Map<string, TMetaRow[]>()

    function queryInfos(table: string) {
        return queryAll(`PRAGMA table_info(${table})`).rows as TMetaRow[]
    }

    function tryGet(table: string) {
        if (!mTableCache.has(table)) {
            mTableCache.set(table, queryInfos(table))
        }

        return mTableCache.get(table)!
    }

    function getTableFields(table: string) {
        const rows = tryGet(table)
        return rows.map(v => v.name)
    }

    function getFieldType(table: string, field: string) {
        const rows = tryGet(table)
        return rows.filter(v => v.name === field).map(v => v.type)[0]
    }

    return {
        getTableFields,
        getFieldType,
    }
}


function useSqliteEngine(wasm_base64: { file: any; }) {
    const wasm_buffer = Uint8Array.from(window.atob(wasm_base64['file']), c => c.charCodeAt(0)).buffer;
    const myBlob = new Blob([wasm_buffer], { type: 'application/wasm' });

    const { state: sqlEngine } = useAsyncState(async () => {
        return await initSqlJs({
            locateFile: file => URL.createObjectURL(myBlob)
        })
    }, null);


    return sqlEngine

}

function useDataBase(sqlEngine: Ref<initSqlJs.SqlJsStatic | null>,
    dbFile: Readonly<Ref<Uint8Array | null>>) {
    const db = ref(null as unknown as Database)

    watch([sqlEngine, dbFile], ([sqlEngine, dbFile]) => {
        if (sqlEngine === null || dbFile === null) {
            return
        }

        db.value = new sqlEngine.Database(dbFile);
    })

    return db
}

function initDb(app: App, wasm_base64: { file: any; }, webResourcesServices: TWebResourcesService) {
    const sqlEngine = useSqliteEngine(wasm_base64)
    const dbFile = webResourcesServices.getDbFile()
    const sqlDb = useDataBase(sqlEngine, dbFile)
    const dbFileBs64Storage = useStorage('pybi-db', null as string | null)

    const isReady = computed(() => sqlDb.value !== null)

    return {
        isReady,
        sqlEngine,
        sqlDb,
        dbFileBs64Storage,
    };
}

function initDbxxx(app: App, wasm_base64: { file: any; }, webResourcesServices: TWebResourcesService) {
    const wasm_buffer = Uint8Array.from(window.atob(wasm_base64['file']), c => c.charCodeAt(0)).buffer;


    const sqlEngine = ref(null as unknown as SqlJsStatic)
    const sqlDb = ref(null as unknown as Database)
    const dbFileBs64Storage = useStorage('pybi-db', null as string | null)

    const myBlob = new Blob([wasm_buffer], { type: 'application/wasm' });

    async function loadDbFile() {
        sqlEngine.value = await initSqlJs({
            locateFile: file => URL.createObjectURL(myBlob)
        });

        let fileBs64: string | null = null;

        if (app.dbLocalStorage) {
            if (!dbFileBs64Storage.value) {
                dbFileBs64Storage.value = webResourcesServices.getResource('DbFile');
            }

            fileBs64 = dbFileBs64Storage.value;
        } else {
            dbFileBs64Storage.value = null;
            fileBs64 = webResourcesServices.getResource('DbFile');
        }


        sqlDb.value = new sqlEngine.value.Database(await dbFile2bytes(fileBs64));
    }

    const { isReady } = useAsyncState(loadDbFile, null);

    return {
        isReady,
        sqlEngine,
        sqlDb,
        dbFileBs64Storage,
    };
}


